﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using UserDefineForm.dao.orm;
using System.Data.SqlClient;
using System.Data;
using UserDefineForm.exception;

namespace UserDefineForm.dao.supplierform
{
    public class ListItemDAO
    {
        private SqlConnection sqlConnection;

        public ListItemDAO(SqlConnection conn)
        {
            this.sqlConnection = conn;
        }

        private ListItemORM createListItemORMFromReader(IDataReader reader)
        {
            ListItemORM item = new ListItemORM();
            try
            {
                if (!reader.IsDBNull(reader.GetOrdinal("Id"))) item.Id = (int)reader["Id"];
                if (!reader.IsDBNull(reader.GetOrdinal("ControlId"))) item.ControlId = (int)reader["ControlId"];
                if (!reader.IsDBNull(reader.GetOrdinal("Value"))) item.Value = (string)reader["Value"];
                
            }
            catch (Exception ex)
            {
                // wrap it and rethrow
                throw new DataReadException(ex.Message);
            }
            return item;
        }

        public List<ListItemORM> getAllListItemByControlId(int controlId)
        {

            SqlDataReader reader = null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("GetAllListItemByControlId", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.Add(new SqlParameter("@controlId", controlId));

                reader = sqlCmd.ExecuteReader();

                List<ListItemORM> result = new List<ListItemORM>();

                while (reader.Read())
                {
                    ListItemORM control = createListItemORMFromReader(reader);
                    result.Add(control);
                }
                return result;
            }
            finally
            {
                if (sqlConnection != null) sqlConnection.Close();
                if (reader != null) reader.Close();
            }
        }

        public void deleteAllListItemByControlId(int controlId)
        {

            SqlDataReader reader = null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("DeleteAllListItemByControlId", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.Add(new SqlParameter("@controlId", controlId));

                sqlCmd.ExecuteNonQuery();

            }
            finally
            {
                if (sqlConnection != null) sqlConnection.Close();
                if (reader != null) reader.Close();
            }
        
        }

        public void insertListItem(ListItemORM item)
        {
            SqlDataReader reader = null;
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCmd = new SqlCommand("InsertListItemValue", sqlConnection);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.Add(new SqlParameter("@id", item.Id));
                sqlCmd.Parameters["@id"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add(new SqlParameter("@controlId", item.ControlId));
                sqlCmd.Parameters.Add(new SqlParameter("@value", item.Value));
                
                sqlCmd.ExecuteNonQuery();

                item.Id = (int)sqlCmd.Parameters["@id"].Value;
            }
            finally
            {
                if (sqlConnection != null) sqlConnection.Close();
                if (reader != null) reader.Close();
            }
        }
    
    }


}
